<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Subqueries : DataMapper ORM - User Guide</title>

<link rel="shortcut icon" type="image/png" href="../images/favicon.png" />
<link rel="stylesheet" type="text/css" media="all" href="../css/userguide.css" />
<link rel="alternate" type="application/rss+xml" title="Datamapper ORM Updates Feed" href="/rss.xml" />

<meta http-equiv="expires" content="-1" />
<meta http-equiv= 'pragma' content="no-cache" />
<meta name="robots" content="all" />

</head>

<body>

<!-- START NAVIGATION -->
<div id="nav"><div id="nav_inner"></div></div>
<div id="nav2"><a name="top">&nbsp;</a><a id="nav_toggle" href="#"><img src="../images/nav_toggle_darker.jpg" width="154" height="43" border="0" title="Toggle Table of Contents" alt="Toggle Table of Contents" /></a></div>
<div id="masthead">
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td><h1>DataMapper ORM</h1></td>
<td id="breadcrumb_right"><a href="toc.html">Table of Contents Page</a></td>
</tr>
</table>
</div>
<!-- END NAVIGATION -->

<!-- START BREADCRUMB -->
<table cellpadding="0" cellspacing="0" border="0" style="width:100%">
<tr>
<td id="breadcrumb">
<a href="/">Datamapper ORM Home</a> &nbsp;&#8250;&nbsp;
<a href="../index.html">User Guide Home</a> &nbsp;&#8250;&nbsp;
Subqueries
</td>
</tr>

</table>
<!-- END BREADCRUMB -->

<br clear="all" />


<!-- START CONTENT -->
<div id="content">


<h1>Subqueries</h1>

<p>Datamapper ORM supports creating and using subqueries to help refine your query, as well as selecting the results of subqueries.</p>

<div class="note">
	<p>Some notes on subqueries:</p>
	<ul>
		<li>The availability of subquery functions may depend on your database.</li>
		<li>If the <var><i>$db_params</i></var> configuration option is set to <dfn>FALSE</dfn>, subqueries will not work.</li>
		<li>Subqueries may have adverse effects on query performance.</li>
		<li>
			Subqueries are fairly difficult.  If you are not comfortable writing subqueries in raw SQL, you will most likely have trouble using the DataMapper methods.
			As they are only usually necessary in very rare occasions, please use normal query methods whenever possible.
		</li>
	</ul>
</div>

<h2 id="basics">Building Subqueries</h2>

<p>
	Subqueries are built using <strong>the exact same ActiveRecord and Datamapper ORM methods</strong> used for normal query generation.
	(They can also be passed in as a manually generated string.)
	For creating a subquery, these methods must be called on a different object than the parent query.
	The object is then passed back into the main query, using one of the various supported methods.
</p>

<h3>Working with the Parent Query</h3>
<p>
	Subqueries can contain references to the parent query, using the special notation <dfn>${parent}.fieldname</dfn>.
	Note that this notation must be written exactly, with the dollar-sign on the outside of the braces.
	Make sure that <var>$escape</var> is set to <dfn>FALSE</dfn> if <dfn>${parent}</dfn> is used with a standard query clause.
</p>
<p class="important">Referencing the parent query by table name <strong>will not work</strong>, as the table name is automatically replaced throughout the query.</p>

<h2>$object->select_subquery($subquery, $alias)</h2>

<p>A subquery can be used as a result column.  In this format, the subquery is always first, and the alias is required.</p>

<div class="important" id="select_func.AR.Bug">
	<strong><em>CodeIgniter ActiveRecord Bug</em></strong>
	<p>
		CodeIgniter has an overly aggressive method for protecting identifiers, and it <strong>cannot</strong> be disabled.
		This may break any attempt to include subqueries in the SELECT statement.
	</p>
	<p>However, with a simple adjustment to the <var>_protect_identifiers</var> method of the <var>DB_driver</var> class, you can get it working again.</p>
	<p><a href="functions.html#Protect.Identifiers.Fix">See the bottom of the functions page for the code modification.</a></p>
</div>

<h3>Example</h3>
<pre>
<var>$u </var><kbd>= new </kbd><var>User</var><kbd>();
</kbd><var>$bugs </var><kbd>= </kbd><var>$u</var><kbd>-&gt;</kbd><var>bug</var><kbd>;

</kbd><samp>// Select the number of open bugs for a user
// Build the subquery - but don't call get()!
</samp><var>$bugs</var><kbd>-&gt;</kbd><var><u>select_func</u></var><kbd>(</kbd><dfn>'COUNT'</dfn><kbd>, </kbd><dfn>'*'</dfn><kbd>, </kbd><dfn>'count'</dfn><kbd>)
</kbd><var>$bugs</var><kbd>-&gt;</kbd><var><u>where_related_status</u></var><kbd>(</kbd><dfn>'closed'</dfn><kbd>, </kbd><var><dfn>FALSE</dfn></var><kbd>)
</kbd><var>$bugs</var><kbd>-&gt;</kbd><var><u>where_related</u></var><kbd>(</kbd><dfn>'user'</dfn><kbd>, </kbd><dfn>'id'</dfn><kbd>, </kbd><dfn>'${parent}.id'</dfn><kbd>);

</kbd><samp>// add to the users query
</samp><var>$u</var><kbd>-&gt;</kbd><var><u>select_subquery</u></var><kbd>(</kbd><var>$bugs</var><kbd>, </kbd><dfn>'bug_count'</dfn><kbd>);
</kbd><var>$u</var><kbd>-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>

<p>If you just want to select the number of related items, please see <a href="getadvanced.html#include_related_count">include_related_count</a></p>


<h2>$object->{query}_subquery($subquery, [$value]) OR $object->{query}_subquery($field, $subquery)</h2>

<p>This format is used to include <var><u>where</u></var> statements, ordering, and <a href="getadvanced.html#Supported.Query.Clauses">other supported query clauses</a>.</p>

<p>The subquery can either be first (such as for <var><u>order_by</u></var> statements) or second (such as <var><u>where</u></var> or <var><u>where_in</u></var> statements).</p>

<p>Example</p>
<pre>
<samp>// This can much, much easier be queried using the normal where_related methods, but it provides an example
</samp><var>$u </var><kbd>= new </kbd><var>User</var><kbd>();

</kbd><var>$sub_u </var><kbd>= new </kbd><var>User</var><kbd>();

</kbd><var>$sub_u</var><kbd>-&gt;</kbd><var><u>select</u></var><kbd>(</kbd><dfn>'id'</dfn><kbd>)-&gt;</kbd><var>where_related_group</var><kbd>(</kbd><dfn>'id'</dfn><kbd>, </kbd><var>1</var><kbd>);

</kbd><var>$u</var><kbd>-&gt;</kbd><var><u>where_in_subquery</u></var><kbd>(</kbd><dfn>'id'</dfn><kbd>, </kbd><var>$sub_u</var><kbd>)-&gt;</kbd><var><u>get</u></var><kbd>();</kbd>
</pre>

<h2>$object->{query}_related_subquery($related_model, $related_field = 'id', $subquery)</h2>

<p>Works the same as above, except the column compared to can come from a related object, not just this object.</p>

<pre>
<samp>// This can much, much easier be queried using the normal where_related methods, but it provides an example
</samp><var>$u </var><kbd>= new </kbd><var>User</var><kbd>();
</kbd><var>$g </var><kbd>= </kbd><var>$u</var><kbd>-&gt;</kbd><var>group</var><kbd>;

</kbd><var>$g</var><kbd>-&gt;</kbd><var><u>where</u></var><kbd>(</kbd><dfn>'id'</dfn><kbd>, </kbd><var>1</var><kbd>);

</kbd><var>$u</var><kbd>-&gt;</kbd><var><u>where_in_related_subquery</u></var><kbd>(</kbd><dfn>'group'</dfn><kbd>, </kbd><var>$g</var><kbd>);</kbd>
</pre>

</div>
<!-- END CONTENT -->


<div id="footer">
<p>
<span id="footer_previous">Previous Topic:&nbsp;&nbsp;<a href=""></a>
&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;</span>
<a href="#top">Top of Page</a>&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
<a href="../index.html">User Guide Home</a>
<span id="footer_next">&nbsp;&nbsp;&nbsp;&middot;&nbsp;&nbsp;
Next Topic:&nbsp;&nbsp;<a href=""></a></span>
</p>
<div id="copyrights">
<p><a href="/">Datamapper ORM</a> &nbsp;&middot;&nbsp; Copyright &copy; 2010-2011 &nbsp;&middot;&nbsp; Harro "WanWizard" Verton</p>
<p><a href="license.html">Other License Information</a></p>
</div>
</div>

<script type="text/javascript" src="../js/mootools.js"></script>
<script type="text/javascript" src="../js/menu.js"></script>
<script type="text/javascript">
<!--
	window.addEvent('domready', function() {

		// Create Menu
		var menu = new Menu({
			basepath: '../',
			pagespath: ''
		});

	});
//-->
</script>
</body>
</html>
